package br.com.lumis.saas.service.consumerclient.persistence.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import lumis.portal.PortalException;
import lumis.portal.UnexpectedException;
import lumis.portal.dao.DaoException;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.util.ITransaction;
import br.com.lumis.saas.SaasRequestParameters;
import br.com.lumis.saas.service.consumerclient.model.Customer;
import br.com.lumis.saas.service.consumerclient.model.Environment;

public class EnvironmentDAO 
{
	private static final String QUERY_BY_CUSTOMER="select * from saas_environment where customerId = ? ";	
	private static final String QUERY_BY_NAME="select * from saas_environment where name = ? ";
	private static final String QUERY_BY_ID="select * from saas_environment where id = ? ";
	private static final String QUERY_UPDATE_NAME="UPDATE saas_environment set name=? where id = ? ";
	private CustomerDAO customerDAO =  new CustomerDAO();
	
	/**
	 * 
	 * @param customer
	 * @param transaction
	 * @return
	 * @throws PortalException
	 */
	public List<Environment> getByCustomer(Customer customer, ITransaction transaction) throws PortalException{
		
		List<Environment> environments = new ArrayList<Environment>();	
		try{								
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement(QUERY_BY_CUSTOMER);
			try{
				statement.setString(1, customer.getId());
				ResultSet resultSet = statement.executeQuery();
				try{
					while(resultSet.next()){						
						Environment environment = new Environment();
						environment.setId(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_ID));
						environment.setName(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_NAME));
						environment.setStatus(resultSet.getInt(SaasRequestParameters.STATUS));
						environment.setSufix(resultSet.getInt(SaasRequestParameters.SUFIX));		
						environment.setType(resultSet.getInt(SaasRequestParameters.TYPE));
						environment.setCustomer(customer);
						
						environments.add(environment);
					}
				}
				finally{
					resultSet.close();
				}
			}
			finally{
				statement.close();
			}
			
			return environments;
		}
		catch (PortalException e){
			throw e;
		}
		catch (Exception e){
			throw new UnexpectedException(e);
		}
	}
	
	/**
	 * 
	 * @param name
	 * @param transaction
	 * @return
	 * @throws PortalException
	 */
	public Environment getByName(String name, ITransaction transaction) throws PortalException{
		Environment environment = null;
		try{								
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement(QUERY_BY_NAME);
			try{
				statement.setString(1, name);
				ResultSet resultSet = statement.executeQuery();
				try{
					if(resultSet.next()){												
						environment = new Environment();
						environment.setId(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_ID));
						environment.setName(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_NAME));	
						Customer customer = customerDAO.getById(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_CUSTOMERID), daoTransactionJdbc);
						environment.setCustomer(customer);
						environment.setStatus(resultSet.getInt(SaasRequestParameters.STATUS));
						environment.setSufix(resultSet.getInt(SaasRequestParameters.SUFIX));		
						environment.setType(resultSet.getInt(SaasRequestParameters.TYPE));
					}
				}
				finally{
					resultSet.close();
				}
			}
			finally{
				statement.close();
			}
			
			return environment;
		}
		catch (PortalException e){
			throw e;
		}
		catch (Exception e){
			throw new UnexpectedException(e);
		}
	}
	
	
	
	public Environment get(String id, ITransaction transaction) throws PortalException{
		Environment environment = new Environment();
		try{								
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement(QUERY_BY_ID);
			try{
				statement.setString(1, id);
				ResultSet resultSet = statement.executeQuery();
				try{
					if(resultSet.next()){												
						environment.setId(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_ID));
						environment.setName(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_NAME));	
						
						
						Customer customer = customerDAO.getById(resultSet.getString(SaasRequestParameters.PAGE_PARAMETER_CUSTOMERID), daoTransactionJdbc);
						environment.setCustomer(customer);
						environment.setUrl(resultSet.getString(SaasRequestParameters.URL));
						environment.setStatus(resultSet.getInt(SaasRequestParameters.STATUS));
						environment.setType(resultSet.getInt(SaasRequestParameters.TYPE));						
						environment.setSufix(resultSet.getInt(SaasRequestParameters.SUFIX));		
						
					}
				}
				finally{
					resultSet.close();
				}
			}
			finally{
				statement.close();
			}
			
			return environment;
		}
		catch (PortalException e){
			throw e;
		}
		catch (Exception e){
			throw new UnexpectedException(e);
		}
	}

	public void updateName(String environmentId, String name,
			ITransaction transaction) throws PortalException {
		ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
		Connection connection;
		try {
			connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection
					.prepareStatement(QUERY_UPDATE_NAME);	

			

			try {
				
				
				
				statement.setString(1,name);
				statement.setString(2, environmentId);

				statement.execute();
			} finally {
				statement.close();
			}

		} catch (SQLException e) {
			throw new UnexpectedException(e);

		} catch (DaoException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

	}
}
